﻿using Md.Api.Entity.ModelDB;
using PetaPoco.NetCore;

namespace Md.Petapoco.Repositories.MdTrainingEducation;
/// <summary>
/// 学生管理仓储
/// </summary>
public class MdStudentRep : BaseRep<MdStudentModel>
{
    /// <summary>
    /// 学生登录
    /// </summary>
    /// <param name="account">学生账号</param>
    /// <param name="pwd">学生密码</param>
    /// <returns>MdStudentModel</returns>
    public MdStudentModel StudentLogin(string account, string pwd)
    {
        #region ===sql===
        string sql = string.Format(@"
            select student_id
                ,student_avatar
                ,student_name
                ,student_account
                ,student_phone
                ,student_age
                ,student_sex
                ,student_idcard
                ,subject_id
                ,subject_name
                ,status
                ,del_flag
                ,create_by
                ,create_time
                ,update_by
                ,update_time
                ,remark
            from md_student
            where 1 = 1 and student_name=@0 and student_pwd_md5=@1 
         ");
        #endregion

        return MdTrainingEducationDB.GetInstance().SingleOrDefault<MdStudentModel>(sql, account, pwd);
    }
    /// <summary>
    /// 通过学生编号获取学生信息
    /// </summary>
    /// <param name="studentId">学生编号</param>
    /// <returns>MdStudentModel</returns>
    public MdStudentModel GetMdStudentByStudentId(long? studentId)
    {
        #region ===sql===
        string sqlStr = string.Format(@"
            select student_id
                ,student_avatar
                ,student_name
                ,student_account
                ,student_phone
                ,student_age
                ,student_sex
                ,student_idcard
                ,subject_id
                ,subject_name
                ,status
                ,del_flag
                ,create_by
                ,create_time
                ,update_by
                ,update_time
                ,remark
            from md_student
            where 1 = 1 and student_id = @0
        ");
        #endregion

        return MdTrainingEducationDB.GetInstance().SingleOrDefault<MdStudentModel>(sqlStr, studentId);
    }
    /// <summary>
    /// 获取学生列表数据
    /// </summary>
    /// <param name="orderBy">排序字段</param>
    /// <param name="isDesc">是否倒序</param>
    /// <param name="model">筛选条件</param>
    /// <returns>IEnumerable<MdStudentModel></returns>
    public IEnumerable<MdStudentModel> GetMdStudentList(string? orderBy, bool? isDesc, MdStudentModel model)
    {
        string sqlWhere = string.Empty;

        #region ===where===
        if (model.StudentId > 0)
        {
            sqlWhere += " and student_id = @0 ";
        }
        if (!string.IsNullOrWhiteSpace(model.StudentName))
        {
            sqlWhere += " and student_name like @1 ";
        }
        if (!string.IsNullOrWhiteSpace(model.StudentAccount))
        {
            sqlWhere += " and student_account like @2 ";
        }
        if (!string.IsNullOrWhiteSpace(model.StudentPhone))
        {
            sqlWhere += " and student_phone like @3 ";
        }
        if (!string.IsNullOrWhiteSpace(model.StudentSex))
        {
            sqlWhere += " and student_sex = @4 ";
        }
        if (!string.IsNullOrWhiteSpace(model.StudentIdcard))
        {
            sqlWhere += " and student_idcard like @5 ";
        }
        if (model.SubjectId > 0)
        {
            sqlWhere += " and subject_id = @6 ";
        }
        if (!string.IsNullOrWhiteSpace(model.SubjectName))
        {
            sqlWhere += " and subject_name like @7 ";
        }
        if (!string.IsNullOrWhiteSpace(model.Status))
        {
            sqlWhere += " and status = @8 ";
        }
        if (!string.IsNullOrWhiteSpace(model.DelFlag))
        {
            sqlWhere += " and del_flag = @9 ";
        }
        if (!string.IsNullOrWhiteSpace(model.CreateBy))
        {
            sqlWhere += " and create_by = @10 ";
        }
        if (!string.IsNullOrWhiteSpace(model.UpdateBy))
        {
            sqlWhere += " and update_by = @11 ";
        }
        #endregion

        #region ===sql===
        string sqlStr = string.Format(@"
            select student_id
                ,student_avatar
                ,student_name
                ,student_account
                ,student_phone
                ,student_age
                ,student_sex
                ,student_idcard
                ,subject_id
                ,subject_name
                ,status
                ,del_flag
                ,create_by
                ,create_time
                ,update_by
                ,update_time
                ,remark
            from md_student
            where 1 = 1 {0} {1}
         ", sqlWhere, GetOrderByDesc(orderBy, isDesc));
        #endregion

        return MdTrainingEducationDB.GetInstance().Query<MdStudentModel>(
            sqlStr,
            model.StudentId,
            string.Format(@"%{0}%", model.StudentName),
            string.Format(@"%{0}%", model.StudentAccount),
            string.Format(@"%{0}%", model.StudentPhone),
            model.StudentSex,
            string.Format(@"%{0}%", model.StudentIdcard),
            model.SubjectId,
            string.Format(@"%{0}%", model.SubjectName),
            model.Status,
            model.DelFlag,
            model.CreateBy,
            model.UpdateBy); ;
    }
    /// <summary>
    /// 分页获取学生列表数据
    /// </summary>
    /// <param name="orderBy">排序字段</param>
    /// <param name="isDesc">是否倒序</param>
    /// <param name="pageIndex">当前页</param>
    /// <param name="pageSize">每页条数</param>
    /// <param name="model">筛选条件</param>
    /// <returns>Page<MdStudentModel></returns>
    public Page<MdStudentModel> GetMdStudentPageList(string? orderBy, bool? isDesc, int pageIndex, int pageSize, MdStudentModel model)
    {
        string sqlWhere = string.Empty;

        #region ===where===
        if (model.StudentId > 0)
        {
            sqlWhere += " and student_id = @0 ";
        }
        if (!string.IsNullOrWhiteSpace(model.StudentName))
        {
            sqlWhere += " and student_name like @1 ";
        }
        if (!string.IsNullOrWhiteSpace(model.StudentAccount))
        {
            sqlWhere += " and student_account like @2 ";
        }
        if (!string.IsNullOrWhiteSpace(model.StudentPhone))
        {
            sqlWhere += " and student_phone like @3 ";
        }
        if (!string.IsNullOrWhiteSpace(model.StudentSex))
        {
            sqlWhere += " and student_sex = @4 ";
        }
        if (!string.IsNullOrWhiteSpace(model.StudentIdcard))
        {
            sqlWhere += " and student_idcard like @5 ";
        }
        if (model.SubjectId > 0)
        {
            sqlWhere += " and subject_id = @6 ";
        }
        if (!string.IsNullOrWhiteSpace(model.SubjectName))
        {
            sqlWhere += " and subject_name like @7 ";
        }
        if (!string.IsNullOrWhiteSpace(model.Status))
        {
            sqlWhere += " and status = @8 ";
        }
        if (!string.IsNullOrWhiteSpace(model.DelFlag))
        {
            sqlWhere += " and del_flag = @9 ";
        }
        if (!string.IsNullOrWhiteSpace(model.CreateBy))
        {
            sqlWhere += " and create_by = @10 ";
        }
        if (!string.IsNullOrWhiteSpace(model.UpdateBy))
        {
            sqlWhere += " and update_by = @11 ";
        }
        #endregion

        #region ===sql===
        string sqlStr = string.Format(@"
            select student_id
                ,student_avatar
                ,student_name
                ,student_account
                ,student_phone
                ,student_age
                ,student_sex
                ,student_idcard
                ,subject_id
                ,subject_name
                ,status
                ,del_flag
                ,create_by
                ,create_time
                ,update_by
                ,update_time
                ,remark
            from md_student
            where 1 = 1 {0} {1}
         ", sqlWhere, GetOrderByDesc(orderBy, isDesc));
        #endregion

        return MdTrainingEducationDB.GetInstance().Page<MdStudentModel>(
            pageIndex,
            pageSize,
            sqlStr,
            model.StudentId,
            string.Format(@"%{0}%", model.StudentName),
            string.Format(@"%{0}%", model.StudentAccount),
            string.Format(@"%{0}%", model.StudentPhone),
            model.StudentSex,
            string.Format(@"%{0}%", model.StudentIdcard),
            model.SubjectId,
            string.Format(@"%{0}%", model.SubjectName),
            model.Status,
            model.DelFlag,
            model.CreateBy,
            model.UpdateBy);
    }
    /// <summary>
    /// 通过学生编号更新学生信息
    /// </summary>
    /// <param name="model">请求实体</param>
    /// <returns>int</returns>
    public long UpdateMdStudentByStudentId(MdStudentModel model)
    {
        var setList = new List<string>();

        #region ===set===
        setList.Add(" student_avatar = @2 ");

        setList.Add(" student_name = @3 ");

        setList.Add(" student_account = @4 ");

        setList.Add(" student_phone = @5 ");

        setList.Add(" student_age = @6 ");

        setList.Add(" student_sex = @7 ");

        setList.Add(" student_idcard = @8 ");

        setList.Add(" subject_id = @9 ");

        setList.Add(" subject_name = @10 ");

        setList.Add(" status = @11 ");

        setList.Add(" update_by = @12 ");

        setList.Add(" update_time = @13 ");

        setList.Add(" remark = @14 ");
        #endregion

        #region ===sql===
        string sqlStr = string.Format(@"
                set {0}
                where 1 = 1 and del_flag = @0 and student_id = @1
         ", string.Join(",", setList));
        #endregion

        var res = MdTrainingEducationDB.GetInstance().Update<MdStudentModel>(
            sqlStr,
            model.DelFlag,
            model.StudentId,
            model.StudentAvatar,
            model.StudentName,
            model.StudentAccount,
            model.StudentPhone,
            model.StudentAge,
            model.StudentSex,
            model.StudentIdcard,
            model.SubjectId,
            model.SubjectName,
            model.Status,
            model.UpdateBy,
            model.UpdateTime,
            model.Remark
         );

        return res;
    }
}

